//Documentation complete
/**
 * @author Jason Funk
 * @version 2009FE22
 */

package broker;

import problemDomain.*;
import static problemDomain.ErrorBean.*;
import static problemDomain.ErrorBean.ErrorCode.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Broker for menus.
 */
public class MenuBroker extends Broker<MenuBean> {
	
	/* Singleton Constructor */
	
	private static MenuBroker broker;
	
	/**
	 * Default constructor. Calls the openConnection method.
	 * @throws ErrorBean
	 */
	private MenuBroker () throws ErrorBean {
		insertSQL =
		updateSQL = null;
		deleteMenuSQL = null;
	}
	
	/**
	 * Retrieves the static MenuBroker instance.
	 * @return static MenuBroker
	 * @throws ErrorBean
	 */
	public synchronized static MenuBroker getBroker () throws ErrorBean {
		if (broker == null)
			broker = new MenuBroker();
		return broker;
	}
	
	/* END Singleton Constructor */
	
	
	/* Prepared Statements */
	
	private PreparedStatement insertSQL;
	private PreparedStatement getInsertSQL () throws ErrorBean {
		return prepare(insertSQL, "INSERT INTO menu SET name = ?, description = ?, active = ?;");
	}
	
	private PreparedStatement updateSQL;
	private PreparedStatement getUpdateSQL () throws ErrorBean {
		return prepare(updateSQL, "UPDATE menu SET name = ?, description = ?, active = ? WHERE menu_id = ?;");
	}
	
	private CallableStatement deleteMenuSQL;
	private CallableStatement getDeleteMenuSQL () throws ErrorBean {
		return prepare(deleteMenuSQL, "CALL sp_delete_menu(?);");
	}
	
	private PreparedStatement menuNameSQL;
	private PreparedStatement getMenuNameSQL () throws ErrorBean {
		return prepare(menuNameSQL, "SELECT name FROM menu WHERE menu_id = ?");
	}
	
	/* END Prepared Statements */
	
	
	/* (Broker) Methods */
	
	@Override
	public synchronized boolean persist (MenuBean mb) throws ErrorBean {
		mb.isValid();
		
		int menu_id = mb.getId();
		String name = mb.getName();
		String description = mb.getDescription();
		List<MenuItemBean> menu_items = mb.getItemList();
		Boolean active = mb.getActive();
		
		if (menu_id == 0) {
			try {
				PreparedStatement insertSQL = getInsertSQL();
				insertSQL.setString(1, name);
				insertSQL.setString(2, description);
				insertSQL.setBoolean(3, active);
				insertSQL.execute();
				
				MenuItemBroker mib = MenuItemBroker.getBroker();
				if (menu_items != null)
					for(int i=0; i<menu_items.size(); i++)
						mib.persist(menu_items.get(i));
				
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_INSERT_ERROR,
					e.getMessage(),
					this);
			}
		} else if (menu_id > 0) {
			try {
				PreparedStatement updateSQL = getUpdateSQL();
				updateSQL.setString(1, name);
				updateSQL.setString(2, description);
				updateSQL.setBoolean(3, active);
				updateSQL.setInt(4, menu_id);
				updateSQL.execute();
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_UPDATE_ERROR,
					e.getMessage(),
					this);
			}
		}
		return false;
	}

	@Override
	public synchronized boolean remove(MenuBean mb) throws ErrorBean	{
		int menu_id = mb.getId();
		
		try {
			CallableStatement deleteMenuSQL = getDeleteMenuSQL();
			deleteMenuSQL.setInt(1, menu_id);
			deleteMenuSQL.executeQuery();
			return true;
		} catch(SQLException e) {
			throwError(
				BROKER_DELETE_ERROR,
				e.getMessage(),
				this);
			return false;
		}
	}
	
	@Override
	public synchronized List<MenuBean> search (MenuBean searchBean) throws ErrorBean {
		List<MenuBean> search = new ArrayList<MenuBean>();
		MenuBean mb;
		
		boolean isFirst = true;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM menu");
		
		//Search by id
		
		int id = searchBean.getId();
		if (id > 0) {
			sql.append(appendClause("menu_id = " + id, isFirst));
		}
		else {
			//Search by Menu name
			String name = searchBean.getName();
			if (!"".equals(name)) {
				sql.append(appendClause("name LIKE '%" + name + "%'", isFirst));
				isFirst = false;
			}
			
			//Search by Menu active status
			Boolean active = searchBean.getActive();
			if (active != null) {
				sql.append(appendClause(" active = " + active, isFirst));
				isFirst = false;
			}
		}
		
		sql.append(';');
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					mb = new MenuBean();
					int menu_id = results.getInt("menu_id");
					mb.setId(menu_id);
					mb.setName(results.getString("name"));
					mb.setDescription(results.getString("description"));
					mb.setActive(results.getBoolean("active"));
					
					//Retrieve the menu item list
					MenuItemBroker mib = MenuItemBroker.getBroker();
					MenuItemBean searchMenuItemBean = new MenuItemBean();
					searchMenuItemBean.setMenu(new MenuBean(menu_id));
					mb.setItemList(mib.search(searchMenuItemBean));
					
					search.add(mb);
				}
			}			
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	@Override
	public synchronized List<MenuBean> listAll() throws ErrorBean {
		List<MenuBean> search = new ArrayList<MenuBean>();
		MenuBean mb;
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM menu;");
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
			
				while (results.next()) {
					mb = new MenuBean();
					int menu_id = results.getInt("menu_id");
					mb.setId(menu_id);
					mb.setName(results.getString("name"));
					mb.setDescription(results.getString("description"));
					mb.setActive(results.getBoolean("active"));
					
					//Retrieve the menu item list
					MenuItemBroker mib = MenuItemBroker.getBroker();
					MenuItemBean searchMenuItemBean = new MenuItemBean();
					searchMenuItemBean.setMenu(new MenuBean(menu_id));
					mb.setItemList(mib.search(searchMenuItemBean));
					
					search.add(mb);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	/* END (Broker) Methods */
	
	
	/* Methods */
	
	/**
	 * Returns the menu name for a <code>MenuBean</code> with the given ID.
	 * @param mb the <code>MenuBean</code>
	 * @return the name of the <code>MenuBean</code>
	 * @throws ErrorBean if the ID is invalid
	 */
	public String getMenuName (MenuBean mb) throws ErrorBean {
		try {
			PreparedStatement stmt = getMenuNameSQL();
			stmt.setInt(1, mb.getId());
			ResultSet rs = stmt.executeQuery();
			rs.next();
			return rs.getString("name");
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
			return null;
		}
	}
	
	/* END Methods */
	
}
